# -*- coding: utf-8 -*-
"""
Created on Wed Sep  9 15:21:15 2020

@author: Xinning XN Lu
"""
#from flask import Flask, request, current_app, jsonify
from flask import Blueprint, request, current_app, jsonify
import time
import pandas as pd
from sklearn.externals import joblib
import numpy as np
import ast
import math
from operator import itemgetter
import warnings

warnings.filterwarnings('ignore')
#app = Flask(__name__)

# %%

k3 = joblib.load(r"/home/dcos/demo/api/km_3_0913.pkl")
k2 = joblib.load(r"/home/dcos/demo/api/km_2_0913.pkl")

'''
计算家中孩子的年龄差距 age_dif
年龄差距为家中最大的孩子和家中最小的孩子的年龄差。

分类变量age_dif_g
-1 ———— 独生子女家庭
0  ———— 多子女家庭且子女年龄差距小于等于7岁
1  ———— 多子女家庭且子女年龄差距大于7岁
'''


def age_dif(child_num, Q32_1_AGE, Q32_2_AGE, Q32_3_AGE):
    if child_num == 1:
        age_dif = -1
    else:
        age_dif = max(Q32_1_AGE, Q32_2_AGE, Q32_3_AGE) - min(Q32_1_AGE, Q32_2_AGE, Q32_3_AGE)
    return age_dif


# 孩子年龄分段
def derive_fare_2(x):
    if 0 <= x <= 6:
        return 1
    elif 6 < x <= 12:
        return 2
    elif 12 < x <= 18:
        return 3

# %% 常数
# 各阶段教育费用参数
edu_info = pd.DataFrame({'edu_stage': ['Kid', 'Pri', 'Mid', 'High', 'Bachelor', 'Master'],  # 教育阶段
                         'edu_start_age': [4, 7, 13, 16, 19, 23],  # 入学年龄
                         'edu_end_age': [7, 13, 16, 19, 23, 25],  # 毕业年龄
                         'stage_misc_fee_in': [6000, 3000, 4000, 3000, 6200, 12000],  # 国内学杂费
                         'daily_consp_in': [0, 0, 0, 0, 20000, 36000],  # 国内独立生活费
                         'extra_edu_spd_in': [5600, 7000, 6400, 4000, 3000, 3000],  # 国内课外教育费
                         'stage_misc_fee_abd': [60000, 20000, 23000, 250000, 300000, 300000],  # 国外学杂费
                         'daily_consp_abd': [0, 0, 0, 150000, 180000, 180000],  # 国外独立生活费
                         'extra_edu_spd_abd': [5000, 5000, 5000, 5000, 5000, 5000]})  # 国外课外教育费
# 教育通胀率
infla_rate_edu = 0.01

# 保障覆盖年数
secur_ylim = 15

# 保障覆盖到年龄
cover_age = 80

# 赡养父母系数
chi_par_exp_pct_dic = {'par_fin':[-3,1,2,3,4], # 父母经济情况
                       'chi_par_exp_pct':[0,0.1,0.4,0.6,0.9]}# 花费比例
#%% 函数
# 调整输出项函数
# 调整一级参数输出值
def adjust_fc_par(x):
    '''
    Description
    ----------

    对一级参数的计算值和显示值之间的调整：（计算值以元为单位，显示值以万元为单位）
    如果保额小于10万，则输出以万元为单位后四舍五入的取值；
    如果保额大于10万，则输出以万元为单位，5万为最小单位长度的取值。

    Parameters
    ----------
    x : 公式计算值（元）

    Returns
    -------
    x_adj : 输出显示值（万元）
    '''
    x = round(x / 10000)
    if x <= 10:
        x_adj = x
    else:
        x_adj = (math.ceil(x / 5)) * 5
    return int(x_adj)


# 调整二、三级参数输出值
def adjust_stc_par(x, x_adj, y):
    '''
    Description
    ----------
    对二、三级参数，基于其计算值本身以及一级参数的计算值和输出值，进行调整计算出其输出值。
    调整方式为 y_adj = y*x_adj/x （万元） “四舍六入五成双”

    Parameters
    ----------
    x : 一级参数计算值
    x_adj : 一级参数输出值
    y : 二级或三级参数计算值

    Returns
    -------
    y_adj : 二级或三级参数计算值
    '''
    if x == 0:
        y_adj = y
    else:
        y_adj = round(y * x_adj / x)
    return y_adj

'''
   家庭收入水平（教育险）
   1    低收入            家庭总收入 <= 30万
   2    中低收入          家庭总收入 <= 36.8万
   3    中等收入          家庭总收入 <= 74万
   4    中高收入          家庭总收入 <= 147万
   5    高收入            家庭总收入 <= 300万
   6    超高收入          家庭总收入 > 300万
   '''

# 收入范围与收入水平对照系数

def income_level_edu(income):
    if income <= 300000:
        return 1
    elif income <= 368000:
        return 2
    elif income <= 740000:
        return 3
    elif income <= 1470000:
        return 4
    elif income <= 3000000:
        return 5
    elif income > 3000000:
        return 6

# 教育目标年龄
def edu_age(edu_targ):
    if edu_targ == 'High':
        edu_targ_age = 19
    elif edu_targ == 'Bachelor':
        edu_targ_age = 23
    elif edu_targ == 'Master':
        edu_targ_age = 25
    elif edu_targ == 'None':
        edu_targ_age = 0
    return edu_targ_age



def tcare_par_spd(par_fin,per_avg_exp,par_n):
    chi_par_exp_pct = chi_par_exp_pct_dic['chi_par_exp_pct'][chi_par_exp_pct_dic['par_fin'].index(par_fin)]
    ## 赡养父母花费 = 保障覆盖年数 * 平均生活支出基数 * 子女负担父母花费比例 * 父母数量
    tcare_spd = secur_ylim * per_avg_exp * chi_par_exp_pct * par_n
    return tcare_spd
'''
家庭消费水平 
日常消费注重
1  低  品牌、质量都为0
2  中  品牌质量选一个
3  高  品牌、质量都为1
'''
def consuption_level(brand, quality):
    if (brand == 0) & (quality == 0):
        return 1
    if (brand == 1) & (quality == 1):
        return 3
    else:
        return 2

'''
收入与消费水平系数：
1    ————  1）收入水平：超高/高 + 消费水平：高  2）收入水平：中高/中/中低/低 + 消费水平：中
0.8  ————  2）收入水平：超高/高 + 消费水平：中  2）收入水平：中高/中低/低 + 消费水平：低
0.5  ————  1）收入水平：超高/高/中 + 消费水平：低
1.05 ————  1）收入水平：中高/中/中低/低 + 消费水平：高
'''
def income_consp_index(income,consumption):
    if ((income in [5,6]) & (consumption == 3)) | ((income in [1,2,3,4]) & (consumption == 2)):
        return 1
    elif ((income in [3,5,6]) & (consumption == 1)):
        return 0.5
    elif ((income in [1,2,4]) & (consumption == 1)) | ((income in [5,6]) & (consumption == 2)):
        return 0.8
    else:
        return 1.05

'''
年龄阶段
1  ———— 少年
2  ———— 青年
3  ———— 中年
4  ———— 老年
'''
def age_stage(age):
    if age < 20:
        return 1
    elif age < 35:
        return 2
    elif age < 60:
        return 3
    else:
        return 4

def acc_med_base(age_stage):
    if age_stage == 1:
        return 200000
    elif age_stage == 2:
        return 300000
    elif age_stage == 3:
        return 400000
    elif age_stage == 4:
        return 500000

'''
个人收入范围（重疾、医疗）
1    低收入          个人收入 <= 11万 
2    中低收入        个人收入 <= 18万
3    中收入          个人收入 <= 37万
4    中高收入        个人收入 <= 73万
5    高收入          个人收入 <= 147万
6    超高收入        个人收入 > 147万
'''
# 收入范围与收入水平对照系数
# 个人收入对照表
def per_income_level1(per_income):
    if per_income <= 110000:
        return 1
    elif per_income <= 180000:
        return 2
    elif per_income <= 370000:
        return 3
    elif per_income <= 730000:
        return 4
    elif per_income <= 1470000:
        return 5
    elif per_income > 1470000:
        return 6

# 重疾调整系数
# 0~35岁 —— 1，36岁 —— 0.998，37岁 —— 0.997，38岁 —— 0.996，39岁 —— 0.995，
# 40~57岁 —— 0.994，58岁 —— 0.993，59岁 —— 0.992，大于等于60 —— 0.990

def adjust_index(age):
    if 0 <= age < 36:
        index = 1
    elif age == 36:
        index = 0.998
    elif age == 37:
        index = 0.997
    elif age == 38:
        index = 0.996
    elif age == 39:
        index = 0.995
    elif age < 58:
        index = 0.994
    elif age == 58:
        index = 0.993
    elif age == 59:
        index = 0.992
    elif age >= 60:
        index = 0.990
    return index

# 医疗险tab值调整
# '''
# 默认医疗险保额小于等于200万： 三甲医院
# 默认医疗险保额小于等于400万： VIP治疗
# 默认医疗险保额大于400万： 海外就医
#
# 调整系数：
# VIP治疗 = 1.94 * 三甲医院
# 海外就医 = 2.41 * VIP治疗
# '''

# def medical_adj(med_exp_amt):
#     if med_exp_amt <= 2000000:
#         # 默认三甲医院
#         index = 1
#         med_exp_amt_1 = med_exp_amt
#         med_exp_amt_2 = med_exp_amt * 1.94
#         med_exp_amt_3 = med_exp_amt * 1.94 * 2.41
#     elif med_exp_amt <= 4050000:
#         # 默认VIP治疗
#         index = 2
#         med_exp_amt_1 = med_exp_amt / 1.94
#         med_exp_amt_2 = med_exp_amt
#         med_exp_amt_3 = med_exp_amt * 2.41
#     else:
#         # 默认海外就医
#         index = 3
#         med_exp_amt_1 = med_exp_amt / 1.94 / 2.41
#         med_exp_amt_2 = med_exp_amt / 2.41
#         med_exp_amt_3 = med_exp_amt
#     return index, med_exp_amt_1, med_exp_amt_2, med_exp_amt_3

# 医疗险五个城市等级对应默认保额
med_amt_1 = pd.DataFrame({'inc':[100000,200000,300000,500000,800000,1000000,2000000,3000000,5000000],
                          'med_exp_amt_1_adj':[110,145,180,180,180,205,205,205,205],
                          'med_exp_amt_2_adj':[215,255,255,255,320,395,395,395,395],
                          'med_exp_amt_3_adj':[455,455,455,455,455,455,455,590,865],
                          'med_exp_amt_index':[1,1,1,2,2,2,3,3,3]}).set_index('inc')

med_amt_2 = pd.DataFrame({'inc':[100000,200000,300000,500000,800000,1000000,2000000,3000000,5000000],
                          'med_exp_amt_1_adj':[110,145,180,180,180,205,205,205,205],
                          'med_exp_amt_2_adj':[215,250,250,250,315,395,395,395,395],
                          'med_exp_amt_3_adj':[455,455,455,455,455,455,455,590,865],
                          'med_exp_amt_index':[1,1,1,2,2,2,3,3,3]}).set_index('inc')

med_amt_3 = pd.DataFrame({'inc':[100000,200000,300000,500000,800000,1000000,2000000,3000000,5000000],
                          'med_exp_amt_1_adj':[100,130,170,170,170,200,200,200,200],
                          'med_exp_amt_2_adj':[195,240,240,240,305,380,380,380,380],
                          'med_exp_amt_3_adj':[435,435,435,435,435,435,435,575,845],
                          'med_exp_amt_index':[1,1,1,2,2,2,3,3,3]}).set_index('inc')

med_amt_4 = pd.DataFrame({'inc':[100000,200000,300000,500000,800000,1000000,2000000,3000000,5000000],
                          'med_exp_amt_1_adj':[90,120,155,155,155,190,190,190,190],
                          'med_exp_amt_2_adj':[175,225,225,225,290,365,365,365,365],
                          'med_exp_amt_3_adj':[420,420,420,420,420,420,420,555,830],
                          'med_exp_amt_index':[1,1,1,2,2,2,3,3,3]}).set_index('inc')

med_amt_5 = pd.DataFrame({'inc':[100000,200000,300000,500000,800000,1000000,2000000,3000000,5000000],
                          'med_exp_amt_1_adj':[85,115,150,150,150,190,190,190,190],
                          'med_exp_amt_2_adj':[165,215,215,215,280,360,360,360,360],
                          'med_exp_amt_3_adj':[400,410,410,410,410,410,410,545,820],
                          'med_exp_amt_index':[1,1,1,2,2,2,3,3,3]}).set_index('inc')

'''
年龄阶段2
1  ———— 青年
2  ———— 中年
3  ———— 中老年

'''


def age_stage2(age):
    if age <= 30:
        return 1
    elif age <= 45:
        return 2
    else:
        return 3


'''
个人收入范围2
1    低收入            个人收入 <= 15万
2    中低收入          个人收入 <= 24万
3    中收入            个人收入 <= 53万
4    中高收入          个人收入 <= 106万
5    高收入            个人收入 <= 145万
6    超高收入          个人收入 > 145万
'''

# 收入范围与收入水平对照系数
def income_level2(inc):
    if inc <= 150000:
        return 1
    elif inc <= 240000:
        return 2
    elif inc <= 530000:
        return 3
    elif inc <= 1060000:
        return 4
    elif inc <= 1450000:
        return 5
    elif inc > 1450000:
        return 6

# 较大值输出为1，较小值输出为0
def compare(amt1,amt2):
    if amt1 > amt2:
        return 1
    else:
        return 0

'''
养老tab调整
无忧养老 = 基础养老 * 1.336
舒适养老 = 无忧养老 * 1.725
'''
def endow_adj(index, endow_mon_amt):
    if index == 1:
        # 默认基础养老
        endow_mon_amt_1 = endow_mon_amt
        endow_mon_amt_2 = round(endow_mon_amt * 1.725 / 100) * 100
        endow_mon_amt_3 = round(endow_mon_amt * 1.725 * 1.336 / 100) * 100
    elif index == 2:
        # 默认无忧养老
        endow_mon_amt_1 = round(endow_mon_amt / 1.725 / 100) * 100
        endow_mon_amt_2 = endow_mon_amt
        endow_mon_amt_3 = round(endow_mon_amt * 1.336 / 100) * 100
    elif index == 3:
        # 默认舒适养老
        endow_mon_amt_1 = round(endow_mon_amt / 1.336 / 1.725 / 100) * 100
        endow_mon_amt_2 = round(endow_mon_amt / 1.336 / 100) * 100
        endow_mon_amt_3 = endow_mon_amt

    return endow_mon_amt_1, endow_mon_amt_2, endow_mon_amt_3

predict_api = Blueprint('predict_api', __name__)
@predict_api.route('/predict', methods=['POST'])
def model():
    object_start_time = time.time()

    customer_info = ast.literal_eval(request.json.get('c_info'))

    city_edu_stage_dic = ast.literal_eval(request.json.get('city_edu'))
    inc_edu_stage_dic = ast.literal_eval(request.json.get('inc_edu'))
    infla_rate = request.json.get('infla_rate')

    life_acc_dic = ast.literal_eval(request.json.get('life_acc'))

    criti_ill_index_city = ast.literal_eval(request.json.get('city_ci'))
    criti_ill_index_inc = ast.literal_eval(request.json.get('inc_ci'))

    med_index_city = ast.literal_eval(request.json.get('city_med'))
    med_index_inc = ast.literal_eval(request.json.get('inc_med'))

    elder_index_inc = ast.literal_eval(request.json.get('inc_endw'))
    elder_index_age = ast.literal_eval(request.json.get('age_endw'))
    elder_index_city = ast.literal_eval(request.json.get('city_endw'))

    current_app.logger.info("Transfer data %s:", time.time() - object_start_time)



    data_predict = pd.DataFrame(customer_info, index=[0])
    # 家庭总收入income（数值变量）
    income = customer_info['Q6_1'] + customer_info['Q6_2'] + customer_info['Q6_3'] + customer_info['Q6_7'] + customer_info['Q6_8'] + customer_info['Q6_9']

    '''
    按家庭总收入划分生成分类变量income2
    1 ———— 总收入<20万
    2 ———— 总收入 20万~50万
    3 ————总收入 50万~80万
    4 ———— 总收入 >80万

    相关变量：
    6、请问您的家庭成员年收入约为？
    Q6_1: 本人(税后年收入（万元）)	
    Q6_2: 配偶(税后年收入（万元）)	
    Q6_3: 父亲(税后年收入（万元）)
    Q6_4: 母亲(税后年收入（万元）)	
    Q6_5: 配偶父亲(税后年收入（万元）)	
    Q6_6: 配偶母亲(税后年收入（万元）)
    Q6_7: 第一个孩子(税后年收入（万元）)
    Q6_8: 第二个孩子(税后年收入（万元）)
    Q6_9: 第三个孩子(税后年收入（万元）)
    '''
    if income < 20:
        data_predict['income2'] = 1
    elif income < 50:
        data_predict['income2'] = 2
    elif income < 80:
        data_predict['income2'] = 3
    else:
        data_predict['income2'] = 4

    # 按家庭结构划分群
    '''
    家庭结构分群分类变量Q5
    1 ———— 家庭形成期	有本人+配偶，无子女且本人年龄<60
    2 ———— 家庭成长期	有本人+子女，子女未成年
    3 ———— 家庭成熟期	1）有子女，子女成年，且本人年纪<60岁；2）无子女，无配偶，有父母，本人成年且年龄<60岁
    4 ———— 夕阳期       本人年龄>=60岁
    5 ———— 单身         只有本人且年龄<60岁

    相关变量：
    Q1: 本人年龄（岁）
    5、请问您的家庭成员有？
    Q5_1: 本人
    Q5_2: 配偶
    Q5_3: 父亲
    Q5_4: 母亲
    Q5_5: 配偶父亲
    Q5_6: 配偶母亲
    Q5_7: 第一个孩子
    Q5_8: 第二个孩子
    Q5_9: 第三个孩子
    '''
    # 是否有子女 （有为True）
    child = (customer_info['Q5_7'] + customer_info['Q5_8'] + customer_info['Q5_9']) != 0

    # 是否有配偶 （有为True）
    couple = customer_info['Q5_2'] == 1

    # 是否有父母 （有为True）
    parents = customer_info['Q5_3'] != 0

    # 有子女未成年 （有为True）
    adult = ((customer_info['Q32_1'] <= 18) & (customer_info['Q32_1'] >= 0)) | (
                (customer_info['Q32_2'] <= 18) & (customer_info['Q32_2'] >= 0)) | (
                        (customer_info['Q32_3'] <= 18) & (customer_info['Q32_3'] >= 0))

    # 是否未满60岁 （未满60岁为True）
    age = (customer_info['Q1'] < 60)

    if (age & (child == False) & (couple)):
        # 家庭形成期 —— 1
        Q5 = 1;
        label = '1#1'
    elif ((age) & (child) & (adult)):
        # 家庭成长期 —— 2
        Q5 = 2
    elif ((age) & (child == False) & (couple == False) & (parents == False)):
        # 单身群 —— 5
        Q5 = 5;
        label = '5#5'
    elif (age == False):
        # 夕阳期 —— 4
        Q5 = 4;
        label = '4#4'
    else:
        # 剩余为家庭成熟期 —— 3
        Q5 = 3

    '''
    本人年龄做分类变量 
    1 ———— 18-25岁
    2 ———— 25-55岁
    3 ———— 大于55岁
    '''
    if 18 <= customer_info['Q1'] < 25:
        data_predict['Q1'] = 1
    elif 25 <= customer_info['Q1'] <= 55:
        data_predict['Q1'] = 2
    elif customer_info['Q1'] > 55:
        data_predict['Q1'] = 3

    """ 
    Q32: 您的孩子目前年龄为？
    Q32_1 —— 第一个孩子年龄
    Q32_2 —— 第二个孩子年龄
    Q32_3 —— 第三个孩子 年龄
    """
    Q32 = ['Q32_1', 'Q32_2', 'Q32_3']

    def derive_fare_1(x):
        if x == -2:
            return np.nan
        else:
            return x

    # 孩子年龄 数值变量储存为Q32_x_AGE
    for var in Q32:
        data_predict[var + '_AGE'] = data_predict[var].apply(derive_fare_1)

    '''
    孩子年龄分类
    1 ———— 0-6岁
    2 ———— 6-12岁
    3 ———— 12-18岁
    4 ———— 大于18岁
    '''

    def derive_fare(x):
        if 0 < x <= 6:
            return 1
        elif 6 < x <= 12:
            return 2
        elif 12 < x <= 18:
            return 3
        elif 18 < x:
            return 4

    for var in Q32:
        data_predict[var] = data_predict[var].apply(derive_fare)

        # %%
    # 分组预测
    # 如果家庭结构属于成熟期
    if Q5 == 3:
        # 家庭收入水平
        '''
        家庭收入水平
        小康 —— 1）居住在一级城市且家庭税后年收入小于50万 
                2）居住在二级城市且家庭税后年收入小于40万 
                3）居住在三级、四级、五级城市且家庭税后年收入小于30万
        富裕 —— 1）居住在一级城市且家庭税后年收入小于100万 
                2）居住在二级城市且家庭税后年收入小于80万 
                3）居住在三级、四级、五级城市且家庭税后年收入小于60万
        高   —— 1）居住在一级城市且家庭税后年收入大于等于100万 
                2）居住在二级城市且家庭税后年收入大于等于80万 
                3）居住在三级、四级、五级城市且家庭税后年收入大于等于60万
        '''
        if customer_info['city_lev'] == '一级城市':
            if income < 50:
                data_predict['family_label'] = '小康'
            elif income < 100:
                data_predict['family_label'] = '富裕'
            else:
                data_predict['family_label'] = '高'
        elif customer_info['city_lev'] == '二级城市':
            if income < 40:
                data_predict['family_label'] = '小康'
            elif income < 80:
                data_predict['family_label'] = '富裕'
            else:
                data_predict['family_label'] = '高'
        elif customer_info['city_lev'] in ['三级城市', '四级城市', '五级城市']:
            if income < 30:
                data_predict['family_label'] = '小康'
            elif income < 60:
                data_predict['family_label'] = '富裕'
            else:
                data_predict['family_label'] = '高'
        if data_predict['family_label'].iloc[0] == '富裕':
            data_predict[['Q49_1','Q49_2','Q49_3','Q49_4','Q49_5','Q49_6']] = [[0,1,0,0,0,0]]
        if data_predict['family_label'].iloc[0] == '小康':
            data_predict[['Q49_1','Q49_2','Q49_3','Q49_4','Q49_5','Q49_6']] = [[1,6,2,3,1,2]]
        # 分出家庭收入水平为高的组，小分组标签为2
        if data_predict['family_label'].iloc[0] == '高':
            label = '3#2'
        else:
            c3_cols = ['Q8', 'Q17', 'Q26', 'Q34_1', 'Q34_2', 'Q34_3', 'Q49_1', 'Q49_2', 'Q49_3', 'Q49_4', 'Q49_5',
                       'Q49_6', 'income2', 'Q32_1', 'family_label']
            group = k3.predict(data_predict[c3_cols])[0]
            label = '3#{0}'.format(group)
    # 如果家庭结构属于成长期
    elif Q5 == 2:
        # 子女个数
        data_predict['chi_num'] = customer_info['Q5_7'] + customer_info['Q5_8'] + customer_info['Q5_9']

        '''
        第33题   您孩子的课外教育花费（包括课外辅导、兴趣班等）每年约为 
        Q33_1 第一个孩子 
        Q33_2 第二个孩子 
        Q33_3 第三个孩子
        '''
        Q33 = ['Q33_1', 'Q33_2', 'Q33_3']

        data_predict[Q33] = data_predict[Q33].replace(-2, 0)

        # Q33处理为家庭在孩子课外教育上的总花费
        Q33 = (data_predict['Q33_1'] + data_predict['Q33_2'] + data_predict['Q33_3']).values[0]

        '''
        对家庭课外教育总花费做分类Q33
        0  ———— 0
        1  ———— 0-1万
        2  ———— 1-5万
        3  ———— 5-10万
        4  ———— 10-20万
        5  ———— 大于20万
        '''
        if Q33 == 0:
            data_predict['Q33'] = 0
        elif 0 < Q33 <= 1:
            data_predict['Q33'] = 1
        elif 1 < Q33 <= 5:
            data_predict['Q33'] = 2
        elif 5 < Q33 <= 10:
            data_predict['Q33'] = 3
        elif 10 < Q33 <= 20:
            data_predict['Q33'] = 4
        else:
            data_predict['Q33'] = 5

        # 计算孩子年龄差距以及所属分组
        data_predict['age_dif'] = data_predict.apply(
            lambda x: age_dif(x['chi_num'], x['Q32_1_AGE'], x['Q32_2_AGE'], x['Q32_3_AGE']), axis=1)
        # 分出多子女且子女年龄差大于等于7岁的组，小分组标签为3
        if (data_predict['age_dif'].iloc[0] >= 7) & (data_predict['chi_num'].iloc[0] > 1):
            label = '2#3'
        else:
            c2_cols = ['Q1', 'Q8', 'Q17', 'Q26', 'Q29', 'Q34_1', 'Q34_2', 'Q34_3', 'income2', 'Q32_min_g']
            data_predict['Q32_min'] = data_predict[['Q32_1_AGE', 'Q32_2_AGE', 'Q32_3_AGE']].apply(lambda x: x.min(),
                                                                                                  axis=1)
            data_predict['Q32_min_g'] = data_predict['Q32_min'].apply(derive_fare_2)
            group = k2.predict(data_predict[c2_cols])[0]
            label = '2#{0}'.format(group)

    current_app.logger.info("Model time %s:", time.time() - object_start_time)

    #%% 计算保额公式
    object_start_time = time.time()
    #%% 变量处理
    '''
    Q1: 本人年龄
    Q5_3: 父母数量
    Q6: 收入
    Q8: 父母经济状况
    Q32: 孩子年龄
    '''
    # 收入以元为单位
    customer_info['Q6_1'] = 10000 * customer_info['Q6_1']
    customer_info['Q6_2'] = 10000 * customer_info['Q6_2']
    customer_info['Q6_3'] = 10000 * customer_info['Q6_3']
    customer_info['Q6_7'] = 10000 * customer_info['Q6_7']
    customer_info['Q6_8'] = 10000 * customer_info['Q6_8']
    customer_info['Q6_9'] = 10000 * customer_info['Q6_9']
    # 父母年龄:用本人+25替代
    customer_info['par_age'] = customer_info['Q1'] + 25


    df = pd.DataFrame(customer_info, index=[0])

    t0 = time.time()
    df.rename(columns={'Q10':'curr_debt'},inplace=True)

    # 孩子个数
    chi_num = customer_info['Q5_7'] + customer_info['Q5_8'] + customer_info['Q5_9']

    # 家庭年总收入 = 家庭各成员年总收入之和
    fam_ann_inc = customer_info['Q6_1'] + customer_info['Q6_2'] + customer_info['Q6_3'] + customer_info['Q6_7'] + customer_info['Q6_8'] + customer_info['Q6_9']
    current_app.logger.info("Prepare data %s:", time.time() - t0)

    #%% 教育险

    # 程序开始时的时间
    time_start=time.time()

    city_edu_stage_tab = pd.DataFrame(city_edu_stage_dic).set_index('city_lev')
    inc_edu_stage_tab = pd.DataFrame(inc_edu_stage_dic).set_index('inc_lev_edu')

    # 相关信息表格

    ci_relate = [itemgetter('Q6_1', 'Q1', 'Q23_1')(customer_info)]
    member = ['own']
    if customer_info['Q5_2'] == 1:
        ci_relate.append(itemgetter('Q6_2', 'Q1', 'Q23_2')(customer_info))
        member.append('cp')
    if customer_info['Q5_3'] != 0:
        ci_relate.append(itemgetter('Q6_3', 'par_age', 'Q23_3')(customer_info))
        member.append('par')
    if customer_info['Q5_7'] == 1:
        ci_relate.append(itemgetter('Q6_7', 'Q32_1', 'Q23_7')(customer_info))
        member.append('fst_chi')
    if customer_info['Q5_8'] == 1:
        ci_relate.append(itemgetter('Q6_8', 'Q32_2', 'Q23_8')(customer_info))
        member.append('snd_chi')
    if customer_info['Q5_9'] == 1:
        ci_relate.append(itemgetter('Q6_9', 'Q32_3', 'Q23_9')(customer_info))
        member.append('thd_chi')

    educate_relate = []; child_n = []
    if customer_info['Q5_7'] == 1:
        educate_relate.append(itemgetter('Q32_1', 'Q34_1','city_lev')(customer_info))
        child_n.append('fst_chi')
    if customer_info['Q5_8'] == 1:
        educate_relate.append(itemgetter('Q32_2','Q34_2','city_lev')(customer_info))
        child_n.append('snd_chi')
    if customer_info['Q5_9'] == 1:
        educate_relate.append(itemgetter('Q32_3','Q34_3','city_lev')(customer_info))
        child_n.append('thd_chi')

    if chi_num !=0:
        df_educate = pd.DataFrame(educate_relate,index=child_n,columns=['age','edu_targ','city_lev'])
        df_educate['fam_ann_inc'] = fam_ann_inc

        df_educate['inc_lev_edu'] = df_educate.apply(lambda x: income_level_edu(x['fam_ann_inc']),axis=1)

        df_educate = df_educate.join(city_edu_stage_tab,on='city_lev')
        df_educate = df_educate.join(inc_edu_stage_tab,on='inc_lev_edu')
        df_educate['edu_targ'].replace({1:'High',2:'Bachelor',3:'Master',0:'None'},inplace=True)


        #%%
        # 教育目标年龄
        df_educate['edu_targ_age'] = df_educate['edu_targ'].apply(edu_age)
        current_app.logger.info("Prepare edu data %s:", time.time() - time_start)

        def edu_table(child_age,income,target_age,
                      extra_edu_spd_index_city,extra_edu_spd_index_inc,daily_cosp_index_city,
                      daily_cosp_index_inc,misc_index_inc):
            '''
            Description
            ----------
            对每一个孩子建立Excel中的对应的教育计算表格，并对其累积学杂费、累积课外教育费、累积生活费进行计算

            Parameters
            ----------
            child_age : 孩子年龄
            income : 家庭年收入
            target_age : 完成最高教育目标时的年龄
            city_level : 城市等级
            income_level : 家庭收入水平（教育）

            Returns
            -------
            accum_misc_fee : 累积学杂费
            accum_diff_stage_extra_edu_spd : 累积课外教育花费
            accum_liv_exp : 累积独立生活费
            '''
            if (child_age<=18) & (child_age>=0):
                t0=time.time()
                #edu = pd.DataFrame(columns=['edu_stage','stage_surp_ann','finish_edu_stage','inland','abroad'])
                #edu['edu_stage'] = ['Kid','Pri','Mid','High','Bachelor','Master']

                #edu = pd.merge(edu,edu_info,how='inner')
                edu = edu_info
                # 各阶段剩余年数
                def surp_stage(end,start):
                    if child_age < start:
                        return end-start
                    elif child_age < end:
                        return end-child_age
                    else:
                        return 0
                # 各阶段剩余年数
                stage_surp_ann = edu.apply(lambda x: surp_stage(x['edu_end_age'],x['edu_start_age']),axis=1)
                # 是否完成此教育阶段
                finish_edu_stage = edu['edu_end_age'].apply(lambda x: 1 if x<=target_age else 0)
                # 是否在国内就读
                inland_h=1 if income < 4500000 else 0
                inland_u=1 if income < 2000000 else 0
                inland_m=1 if income < 1000000 else 0
                edu_inland = np.array([1,1,1,inland_h,inland_u,inland_m])
                edu_abroad = np.array([0,0,0,abs(1-inland_h),abs(1-inland_u),abs(1-inland_m)])

                # 预期各阶段剩余年数 = 各阶段剩余年数 * 是否完成此教育阶段
                # 累计学杂费 = 各阶段剩余年数 * 各阶段学杂费
                accum_misc_fee=sum(stage_surp_ann*finish_edu_stage*edu['stage_misc_fee_in']*edu_inland*misc_index_inc) + sum(stage_surp_ann*finish_edu_stage*edu['stage_misc_fee_abd']*edu_abroad)

                # 累计各阶段课外教育费用 = 各阶段剩余年数 * 各阶段课外教育费用 * 城市等级系数（课外） * 收入水平系数（课外）
                accum_diff_stage_extra_edu_spd = (sum(stage_surp_ann*finish_edu_stage*edu['extra_edu_spd_in']*edu_inland) + \
                    sum(stage_surp_ann*finish_edu_stage*edu['extra_edu_spd_abd']*edu_abroad))*extra_edu_spd_index_city*extra_edu_spd_index_inc

                # 累计日常生活费用（国内） = 各阶段剩余年数 * 各阶段生活费(国内） * 城市等级系数（生活费） * 收入水平系数（生活费）
                accum_liv_exp_in = sum((stage_surp_ann * finish_edu_stage* edu_inland * daily_cosp_index_city * daily_cosp_index_inc)[[3,4,5]] * edu.loc[
                        [3, 4, 5], 'daily_consp_in'])
                #accum_liv_exp_in = sum(edu.loc[[3,4,5],'stage_surp_ann']*edu.loc[[3,4,5],'finish_edu_stage']*edu.loc[[3,4,5],'daily_consp_in']*edu.loc[[3,4,5],'inland'])*daily_cosp_index_city*daily_cosp_index_inc

                # 累计日常生活费用（国外） = 各阶段剩余年数 * 各阶段生活费(国外）
                accum_liv_exp_abd = sum(stage_surp_ann*finish_edu_stage*edu['daily_consp_abd']*edu_abroad)
                accum_liv_exp = accum_liv_exp_in + accum_liv_exp_abd

                current_app.logger.info('one child edu1 var %s',time.time()-t0)

                return accum_misc_fee,accum_diff_stage_extra_edu_spd,accum_liv_exp
            else:
                return (0,0,0)

        '''
        教育险保额调整：
        当家庭总收入<100万时，默认为国内就读
        切换为海外求学时，将edu_target项对应的学习阶段从国内切换成国外
        当家庭总收入>=100万时，默认为海外求学
        切换为国内就读时，所有学习阶段均替换成国内求学
        '''
        def edu_adj(child_age,income,target_age,
                    extra_edu_spd_index_city,extra_edu_spd_index_inc,daily_cosp_index_city,
                    daily_cosp_index_inc,misc_index_inc):
            '''
            对教育险默认的tab进行切换时，对不同学习阶段在国内国外完成进行调整，并且重新计算所需的各项花费

            Parameters
            ----------
            child_age : 孩子年龄
            income : 家庭年收入
            target_age : 完成最高教育目标时的年龄
            city_level : 城市等级
            income_level : 家庭收入水平（教育）

            Returns
            -------
            accum_misc_fee : 累积学杂费
            accum_diff_stage_extra_edu_spd : 累积课外教育花费
            accum_liv_exp : 累积独立生活费
            index : 默认输出tab（1为国内，2为国外）

            '''
            if (child_age<=18) & (child_age>=0):
                t0= time.time()
                #edu = pd.DataFrame(columns=['edu_stage','stage_surp_ann','finish_edu_stage','inland','abroad'])
                #edu['edu_stage'] = ['Kid','Pri','Mid','High','Bachelor','Master']

                #edu = pd.merge(edu,edu_info,how='inner')
                edu = edu_info
                # 各阶段剩余年数
                def surp_stage(end,start):
                    if child_age < start:
                        return end-start
                    elif child_age < end:
                        return end-child_age
                    else:
                        return 0
                # 各阶段剩余年数
                stage_surp_ann=edu.apply(lambda x: surp_stage(x['edu_end_age'], x['edu_start_age']), axis=1)
                #edu['stage_surp_ann']=edu.apply(lambda x: surp_stage(x['edu_end_age'],x['edu_start_age']),axis=1)
                # 是否完成此教育阶段
                finish_edu_stage = edu['edu_end_age'].apply(lambda x: 1 if x <= target_age else 0)
                #edu['finish_edu_stage'] = edu['edu_end_age'].apply(lambda x: 1 if x<=target_age else 0)
                # 是否在国内就读
                if income >= 1000000:
                    index = 2
                    # 默认为国外就读 全部切换成国内
                    inland = np.array([1,1,1,1,1,1])
                    abroad = np.array([0,0,0,0,0,0])
                else:
                    # 默认为国内就读
                    index = 1
                    if target_age == 19:
                        # 预期教育为高中
                        inland = np.array([1,1,1,0,0,0])
                        abroad = np.array([0,0,0,1,1,1])
                    elif target_age == 23:
                        # 预期教育为大学
                        inland = np.array([1,1,1,1,0,0])
                        abroad = np.array([0,0,0,0,1,1])
                    elif target_age == 25:
                        # 预期教育为研究生
                        inland = np.array([1,1,1,1,1,0])
                        abroad = np.array([0,0,0,0,0,1])

                # 预期各阶段剩余年数 = 各阶段剩余年数 * 是否完成此教育阶段
                # 累计学杂费 = 各阶段剩余年数 * 各阶段学杂费
                accum_misc_fee=sum(stage_surp_ann*finish_edu_stage*edu['stage_misc_fee_in']*inland*misc_index_inc) + sum(stage_surp_ann*finish_edu_stage*edu['stage_misc_fee_abd']*abroad)


                #accum_misc_fee=sum(edu['stage_surp_ann']*edu['finish_edu_stage']*edu['stage_misc_fee_in']*edu['inland']*misc_index_inc) + sum(edu['stage_surp_ann']*edu['finish_edu_stage']*edu['stage_misc_fee_abd']*edu['abroad'])

                # 累计各阶段课外教育费用 = 各阶段剩余年数 * 各阶段课外教育费用 * 城市等级系数（课外） * 收入水平系数（课外）
                accum_diff_stage_extra_edu_spd = (sum(stage_surp_ann * finish_edu_stage * edu['extra_edu_spd_in'] * inland) + \
                                                  sum(stage_surp_ann * finish_edu_stage * edu[ 'extra_edu_spd_abd'] * abroad)) * \
                                                 extra_edu_spd_index_city * extra_edu_spd_index_inc
                # accum_diff_stage_extra_edu_spd = (sum(edu['stage_surp_ann']*edu['finish_edu_stage']*edu['extra_edu_spd_in']*edu['inland']) + \
                #     sum(edu['stage_surp_ann']*edu['finish_edu_stage']*edu['extra_edu_spd_abd']*edu['abroad']))*extra_edu_spd_index_city*extra_edu_spd_index_inc

                # 累计日常生活费用（国内） = 各阶段剩余年数 * 各阶段生活费(国内） * 城市等级系数（生活费） * 收入水平系数（生活费）
                accum_liv_exp_in = sum(
                    (stage_surp_ann * finish_edu_stage * inland * daily_cosp_index_city * daily_cosp_index_inc)[
                        [3, 4, 5]] * edu.loc[[3, 4, 5], 'daily_consp_in'])
                #accum_liv_exp_in = sum(edu.loc[[3,4,5],'stage_surp_ann']*edu.loc[[3,4,5],'finish_edu_stage']*edu.loc[[3,4,5],'daily_consp_in']*edu.loc[[3,4,5],'inland'])*daily_cosp_index_city*daily_cosp_index_inc

                # 累计日常生活费用（国外） = 各阶段剩余年数 * 各阶段生活费(国外）
                accum_liv_exp_abd = sum(stage_surp_ann * finish_edu_stage * edu['daily_consp_abd'] * abroad)
                accum_liv_exp = accum_liv_exp_in + accum_liv_exp_abd

                current_app.logger.info('one child edu2 var %s', time.time()-t0)
                return accum_misc_fee,accum_diff_stage_extra_edu_spd,accum_liv_exp,index
            else:
                return (0,0,0,0)


        # 通胀因子 = （1+通胀率） * （预期教育阶段对应结束年龄 - 子女年龄）

        def infla(child_age,target_age):
            if infla_rate_edu == 0:
                edu_infla = 1
            elif infla_rate_edu != 0:
                edu_infla = (1+infla_rate_edu)**(target_age-child_age)
            return edu_infla

        infla_edu = df_educate.apply(lambda x: infla(x['age'], x['edu_targ_age']), axis=1)

        t0=time.time()


        # 分别对三个孩子计算教育险
        # 家庭年收入大于等于100万，默认输出为海外（使用edu_tab）
        if fam_ann_inc >= 1000000:
            df_educate[['stafe_misc_fee_2','accum_diff_stage_extra_edu_spd_2','accum_liv_exp_2']] \
                = df_educate.apply(lambda x: edu_table(x['age'],x['fam_ann_inc'], x['edu_targ_age'],
                                              x['extra_edu_spd_index_city'],x['extra_edu_spd_index_inc'],
                                              x['daily_cosp_index_city'],x['daily_cosp_index_inc'],x['misc_index_inc']),
                                   axis=1, result_type='expand')

            df_educate[['stafe_misc_fee_1','accum_diff_stage_extra_edu_spd_1','accum_liv_exp_1','edu_expt_amt_index']] \
                = df_educate.apply(lambda x: edu_adj(x['age'],x['fam_ann_inc'],x['edu_targ_age'],
                                                     x['extra_edu_spd_index_city'],x['extra_edu_spd_index_inc'],
                                                     x['daily_cosp_index_city'],x['daily_cosp_index_inc'],x['misc_index_inc']),
                                   axis=1, result_type='expand')

        else:
            # 默认输出为国内（使用edu_tab）
            df_educate[['stafe_misc_fee_1','accum_diff_stage_extra_edu_spd_1','accum_liv_exp_1']] \
                = df_educate.apply(lambda x: edu_table(x['age'],x['fam_ann_inc'], x['edu_targ_age'],
                                              x['extra_edu_spd_index_city'],x['extra_edu_spd_index_inc'],
                                              x['daily_cosp_index_city'],x['daily_cosp_index_inc'],x['misc_index_inc']),
                                   axis=1, result_type='expand')

            # 调整值为国外（使用edu_adj)
            df_educate[['stafe_misc_fee_2','accum_diff_stage_extra_edu_spd_2','accum_liv_exp_2','edu_expt_amt_index']] \
                = df_educate.apply(lambda x: edu_adj(x['age'],x['fam_ann_inc'],x['edu_targ_age'],
                                                     x['extra_edu_spd_index_city'],x['extra_edu_spd_index_inc'],
                                                     x['daily_cosp_index_city'],x['daily_cosp_index_inc'],x['misc_index_inc']),
                                   axis=1, result_type='expand')

        current_app.logger.info("Calculate basic edu var %s", time.time() - t0)
        t0=time.time()
        # 教育险理想保额 = 累积学杂费+累积各阶段课外教育费用+累积生活费
        df_educate['edu_expt_amt_1'] = df_educate[['stafe_misc_fee_1','accum_diff_stage_extra_edu_spd_1','accum_liv_exp_1']].apply(lambda x:x.sum(),axis =1)

        # 教育险理想保额（考虑通胀） = 教育险理想保额 * 通胀因子
        df_educate['edu_expt_amt_infla_1'] = df_educate['edu_expt_amt_1'] * infla_edu
        df_educate[['stafe_misc_fee_1','accum_diff_stage_extra_edu_spd_1','accum_liv_exp_1']] = \
            (df_educate[['stafe_misc_fee_1','accum_diff_stage_extra_edu_spd_1','accum_liv_exp_1']].T * infla_edu).T
        # 调整一级参数输出值
        df_educate['edu_expt_amt_infla_1_adj'] = df_educate['edu_expt_amt_infla_1'].apply(adjust_fc_par)

        # 教育险理想保额 = 累积学杂费+累积各阶段课外教育费用+累积生活费
        df_educate['edu_expt_amt_2'] = df_educate[['stafe_misc_fee_2','accum_diff_stage_extra_edu_spd_2','accum_liv_exp_2']].apply(lambda x:x.sum(),axis =1)

        # 教育险理想保额（考虑通胀） = 教育险理想保额 * 通胀因子
        df_educate['edu_expt_amt_infla_2'] = df_educate['edu_expt_amt_2'] * infla_edu
        df_educate[['stafe_misc_fee_2','accum_diff_stage_extra_edu_spd_2','accum_liv_exp_2']] = \
            (df_educate[['stafe_misc_fee_2','accum_diff_stage_extra_edu_spd_2','accum_liv_exp_2']].T * infla_edu).T
        current_app.logger.info("Calculate remain edu var %s", time.time() - t0)
        # 调整一级参数输出值
        t0 = time.time()
        df_educate['edu_expt_amt_infla_2_adj'] = df_educate['edu_expt_amt_infla_2'].apply(adjust_fc_par)

        # 调整二级参数输出值
        edu_list1 = ['stafe_misc_fee_1','accum_diff_stage_extra_edu_spd_1','accum_liv_exp_1']
        edu_list2 = ['stafe_misc_fee_2','accum_diff_stage_extra_edu_spd_2','accum_liv_exp_2']

        for var in edu_list1:
            df_educate['{0}_adj'.format(var)] = df_educate.apply(lambda x: adjust_stc_par(x['edu_expt_amt_infla_1'],
                                                            x['edu_expt_amt_infla_1_adj'],
                                                            x[var]),axis=1)

        for var in edu_list2:
            df_educate['{0}_adj'.format(var)] = df_educate.apply(lambda x: adjust_stc_par(x['edu_expt_amt_infla_2'],
                                                            x['edu_expt_amt_infla_2_adj'],
                                                            x[var]),axis=1)
        current_app.logger.info("Adjust Edu var cost %s", time.time() - t0)
    current_app.logger.info("Edu cost %s:", time.time() - time_start)

    #%% 寿险
    time_start=time.time()
    life_acc_index = pd.DataFrame(life_acc_dic)  #意外医疗花费系数

    '''
    家庭收入水平（寿险、意外险） fam_inc_lev
    1    低收入            家庭总收入 <= 23万 
    2    中低收入          家庭总收入 <= 36.8万
    3    中等收入          家庭总收入 <= 74万
    4    中高收入          家庭总收入 <= 147万
    5    高收入            家庭总收入 <= 300万
    6    超高收入          家庭总收入 > 300万 
    '''
    ## 资产传承
    if fam_ann_inc <= 230000:
        fam_inc_lev = 1
        df['ass_inh_amt'] = 0
    elif fam_ann_inc <= 368000:
        fam_inc_lev = 2
        df['ass_inh_amt'] = 314899
    elif fam_ann_inc <= 740000:
        fam_inc_lev = 3
        df['ass_inh_amt'] = 425897
    elif fam_ann_inc <= 1470000:
        fam_inc_lev = 4
        df['ass_inh_amt'] = 513672
    elif fam_ann_inc <= 3000000:
        fam_inc_lev = 5
        df['ass_inh_amt'] = customer_info['avg_house'] * 100 * 0.3
    elif fam_ann_inc > 3000000:
        fam_inc_lev = 6
        df['ass_inh_amt'] = fam_ann_inc * 2


    '''
    家庭消费水平 
    日常消费注重
    1  低  品牌、质量都为0
    2  中  品牌质量选一个
    3  高  品牌、质量都为1
    '''
    if (customer_info['Q16_5'] == 0) & (customer_info['Q16_4'] == 0):
        consp_lev = 1
    if (customer_info['Q16_5'] == 1) & (customer_info['Q16_4'] == 1):
        consp_lev = 3
    else:
        consp_lev = 2

    inc_consp_lev_index = income_consp_index(fam_inc_lev,consp_lev)


    ### 家庭未来支出=家庭生活支出+子女教育花费+赡养父母花费+子女婚嫁金
    def family_liv_exp(family_income,income_level):
        # 平均家庭生活支出比例 = 人均年消费支出 / 人均年收入
        avg_liv_exp_pct = customer_info['per_avg_ann_exp'] / customer_info['per_avg_ann_inc']
        # 消费水平系数
        fam_consp_index = life_acc_dic['fam_consp_index'][life_acc_dic['inc_lev'].index(income_level)]
        ## 家庭生活支出=保障覆盖年数*当前家庭年收入*平均家庭生活支出比例*收入消费水平调整系数*消费水平系数
        fam_liv_spd = secur_ylim * family_income * avg_liv_exp_pct * inc_consp_lev_index * fam_consp_index
        return fam_liv_spd

    df['fam_liv_spd'] = family_liv_exp(fam_ann_inc,fam_inc_lev)

    ## 固定储蓄（家庭储蓄预测） = 家庭年收入 * 储蓄比例 * 储蓄年限
    # 储蓄年限=Max(0,(age-24));储蓄比例=0.1
    df['fixed_savi'] = fam_ann_inc*0.1*max(customer_info['Q1']-24, 0)
    ## 赡养父母花费 = 保障覆盖年数 * 平均生活支出基数 * 子女负担父母花费比例 * 父母数量
    # 平均生活支出基数 = 个人平均年消费
    df['tcare_par_spd'] = tcare_par_spd(customer_info['Q8'], customer_info['per_avg_ann_exp'], customer_info['Q5_3'])


    ## 子女婚嫁金：
    ## 一个孩子 = 子女婚嫁花费系数 * 子女成家花费
    ## 两个孩子 = 子女婚嫁花费系数 * 子女成家花费 * 1.5
    ## 三个孩子 = 子女婚嫁花费系数 * 子女成家花费 * 1.75
    ## 注： 第二个子女创业费*0.5， 第三个子女创业费*0.25，孩子数量为年龄<=25岁的孩子

    def chi_mar_exp(income_level):
        fir_chi_age = customer_info['Q32_1']
        sec_chi_age = customer_info['Q32_2']
        thi_chi_age = customer_info['Q32_3']
        # 子女成家花费 = 城市房价均价 * 100 * 0.3
        chi_mar_exp_base = customer_info['avg_house'] * 100 * 0.3
        # 子女婚嫁花费系数
        chi_mar_index = life_acc_dic['chi_mar_index'][life_acc_dic['inc_lev'].index(income_level)]

        # 一个孩子子女婚嫁金
        chi_mar_exp_base = chi_mar_exp_base * chi_mar_index

        # 符合年龄限制的孩子个数
        n = sum(np.array([(0<=fir_chi_age<=25),(0<=sec_chi_age<=25),(0<=thi_chi_age<=25)]).astype(int))

        if n == 0:
            return 0
        elif n == 2:
            return chi_mar_exp_base * 1.5
        elif n == 1:
            return chi_mar_exp_base
        else:
            return chi_mar_exp_base * 1.75

    df['chi_marr_spd'] =chi_mar_exp(fam_inc_lev)

    # 子女教育花费 = 子女教育险总和
    if chi_num != 0:
        if fam_ann_inc >= 1000000:
            df['chi_edu_spd'] = sum(df_educate['edu_expt_amt_2'])
        else:
            df['chi_edu_spd'] = sum(df_educate['edu_expt_amt_1'])
    else:
        df['chi_edu_spd'] = 0

    # 家庭寿险理想总额 = 家庭未来支出+资产传承+当前负债-固定储蓄
    # 家庭未来支出 = 家庭生活支出+子女教育花费+赡养父母花费+子女婚嫁金
    df['fam_fur_spd'] = df['fam_liv_spd'] + df['chi_edu_spd'] + df['tcare_par_spd']+ df['chi_marr_spd']
    df['fam_life_amt'] = df['fam_fur_spd'] + df['ass_inh_amt'] + df['curr_debt']*10000 - df['fixed_savi']
    # 当家庭寿险理想总额小于0时，寿险保额为0
    df['fam_life_amt'] = df['fam_life_amt'].apply(lambda x: 0 if x<0 else x)
    df.index=['own']
    fam_life_amt = df['fam_life_amt'].values[0]

    # 相关信息表格

    ci_relate = [itemgetter('Q6_1','Q1','Q23_1')(customer_info)]
    member = ['own']
    if customer_info['Q5_2'] == 1:
        ci_relate.append(itemgetter('Q6_2','Q1','Q23_2')(customer_info))
        member.append('cp')
    if customer_info['Q5_3'] != 0:
        ci_relate.append(itemgetter('Q6_3','par_age','Q23_3')(customer_info))
        member.append('par')
    if customer_info['Q5_7'] == 1:
        ci_relate.append(itemgetter('Q6_7','Q32_1','Q23_7')(customer_info))
        member.append('fst_chi')
    if customer_info['Q5_8'] == 1:
        ci_relate.append(itemgetter('Q6_8','Q32_2','Q23_8')(customer_info))
        member.append('snd_chi')
    if customer_info['Q5_9'] == 1:
        ci_relate.append(itemgetter('Q6_9','Q32_3','Q23_9')(customer_info))
        member.append('thd_chi')

    df_ci = pd.DataFrame(ci_relate,index=member,columns=['inc','age','med'])
    df_ci['city_lev'] = customer_info['city_lev']
    df_ci['inc_lev'] = fam_inc_lev


    # 每个家庭成员的寿险理想保额 = 家庭寿险理想总额 * 该家庭成员在家中的收入占比

    df_ci['inc_pct'] = df_ci['inc'] / fam_ann_inc
    df_ci['life_amt'] = round(fam_life_amt * df_ci['inc_pct'] /10000).astype(int)
    # 无收入成年人寿险为家庭寿险总保额的10%
    df_ci.loc[df_ci['inc'] == 0, 'life_amt'] = int(round(fam_life_amt * 0.1 /10000))
    # 小于等于18岁的家庭成员寿险为50万
    df_ci.loc[df_ci['age']<18,'life_amt'] = 50
    # 小于等于10岁的家庭成员寿险为20万
    df_ci.loc[df_ci['age']<=10,'life_amt'] = 20

    # 对于收入大于等于家庭总收入70%的家庭成员，输出支柱标签support_label=1
    df_ci['support_label'] = 0
    df_ci.loc[df_ci['inc_pct']>=0.7, 'support_label'] = 1

    # 二级、三级参数输出调整（精确到万）
    life_list =['fam_fur_spd','fam_liv_spd','chi_edu_spd','tcare_par_spd','chi_marr_spd','ass_inh_amt','fixed_savi']

    for var in life_list:
        df[var] = int(round(df[var]/10000))

    current_app.logger.info("Life cost %s:", time.time() - time_start)

    #%% 意外保障
    time_start = time.time()
    df_ci = df_ci.join(life_acc_index[['inc_lev','acc_index','acc_med_exp_index']].set_index('inc_lev'),on='inc_lev')

    df_ci['age_stage'] = df_ci['age'].apply(age_stage)

    df_ci['acc_med_base'] = df_ci['age_stage'].apply(acc_med_base)

    ### 个人意外险理想保额 = 个人意外责任（身故）保障 + 个人意外医疗花费

    # 个人意外医疗花费 = 意外医疗花费基数（与个人年龄有关）* 医疗花费系数(家庭) （精确到万元）
    df_ci['acc_med_spd'] = round(df_ci['acc_med_base'] * df_ci['acc_med_exp_index']/10000).astype(int)

    # 个人意外责任保障 = 个人寿险理想保额 * 意外乘数 （精确到万元）
    df_ci['acc_due'] = round(df_ci['life_amt'] * df_ci['acc_index']).astype(int)
    # 年龄小于18，大于60,个人意外身故用50万代替
    df_ci.loc[df_ci[df_ci['age']<=10].index,'acc_due'] = 20
    df_ci.loc[df_ci[df_ci['age']<18].index,'acc_due'] = 50

    # 个人意外险理想保额(精确到万元)
    df_ci['acc_exp_amt'] = round(df_ci['acc_due'] + df_ci['acc_med_spd']).astype(int)

    current_app.logger.info("Acc cost %s:", time.time() - time_start)

    #%% 重疾

    # 程序开始时的时间
    time_start=time.time()

    #criti_ill_city_tab =pd.DataFrame(criti_ill_index_city).set_index('city_lev')
    criti_ill_inc_tab=pd.DataFrame(criti_ill_index_inc)
    care_consp_index_ci = criti_ill_index_city['care_consp_index_ci'][criti_ill_index_city['city_lev'].index(customer_info['city_lev'])]
    med_cost_index_ci = criti_ill_index_city['med_cost_index_ci'][criti_ill_index_city['city_lev'].index(customer_info['city_lev'])]


    # 城市等级与人均护理花费基数对照
    city_care_spd_dic = {'city_lev':['一级城市','二级城市','三级城市','四级城市','五级城市'],
                                     'liv_cost_base':[40788,31605,29625,19101,17101],
                                     'per_ann_inc_base':[96317,70757,70757,42314,40314],
                                     'liv_cost_base_med':[40788,31605,27040,19101,17101]}
    liv_cost_base = city_care_spd_dic['liv_cost_base'][city_care_spd_dic['city_lev'].index(customer_info['city_lev'])]
    per_ann_inc_base = city_care_spd_dic['per_ann_inc_base'][city_care_spd_dic['city_lev'].index(customer_info['city_lev'])]



    # 收入水平与其他花费对照表
    criti_ill_ftab_inc = pd.DataFrame({'per_inc_lev1':[6,5,4,3,2,1],
                                       'op_base':[6500,3500,2300,1400,876,640],
                                       'ihosp_base':[6000,3000,2010,1000,896,100],
                                       'care_base':[80000,80000,72178,65700,40150,0]})

    criti_ill_ftab_inc = criti_ill_ftab_inc.merge(criti_ill_inc_tab).set_index('per_inc_lev1')

    '''
     健康风险：计算Q21生活中坏习惯的项数
    1   低   Q26_7 = 0 (无坏习惯)
    2   中   有1~4项
    3   高   有5~6项
    '''
    '''
    健康风险  康复时间
      低        4年
      中        5年
      高        6年
    ---
    (养老)
    b6: 养生健康消费系数b6
    b10: 疾病管理消费系数b10
    b14: 娱乐消费系数b14
    '''

    count = customer_info['Q21_1']+customer_info['Q21_2']+customer_info['Q21_3']+customer_info['Q21_4']+\
            customer_info['Q21_5']+customer_info['Q21_6']

    if count == 0:
        rec_yrs = 4;b6=1.2;b10=0.8;b14=1.5
    elif 1<= count < 5:
        rec_yrs = 5;b6=1;b10=1;b14=1
    elif count >= 5:
        rec_yrs = 6;b6=0.8;b10=1.4;b14=0.5

    df_ci['inc_lev_1']=df_ci['inc'].apply(per_income_level1)
    # 无收入家庭成员收入水平用家庭收入水平替代
    df_ci.loc[df_ci['inc'] == 0, 'inc_lev_1'] = fam_inc_lev

    df_ci = df_ci.join(criti_ill_ftab_inc,on='inc_lev_1')


    # 门诊费用 = 门诊费用基数 * 医疗花费系数
    df_ci['op_cost'] = round(df_ci['op_base'] * med_cost_index_ci/10000).astype(int)

    # 药品耗材与治疗花费 = （高危重症药品费 + 高危重症治疗费） * 医疗花费系数 * 治疗花费系数 * （1+重疾复发概率）
    # 高危重症药品费=514328， 高危重症治疗费=220000， 重疾复发概率=30%
    df_ci['medi_treat_cost'] = round((514328+220000)/10000 * med_cost_index_ci * df_ci['tre_spd_index_ci'] * (1+0.3)).astype(int)

    # 住院费 = 每天住院花费基数 * 医疗花费系数 * 高危住院天数
    # 高危住院天数 = 20天
    df_ci['ihosp_cost'] = round(df_ci['ihosp_base'] * med_cost_index_ci * 20 /10000).astype(int)

    # 重疾治疗费用 = 门诊费用 + 药品耗材与治疗花费 + 住院费
    df_ci['criti_ill_cost'] = round(df_ci['op_cost'] + df_ci['medi_treat_cost'] + df_ci['ihosp_cost']).astype(int)

    # 医保报销 = 25万
    df_ci['med_claim'] = 25
    # 无医保家庭成员医保报销=0
    df_ci.loc[df_ci['med'] == 2,'med_claim'] = 0

    # 营养费 = 生活花费基数 * 营养费系数 * 营养费占生活支出比例 * 康复时间
    df_ci['nutri_fee'] = round(liv_cost_base * df_ci['nur_spd_index_ci'] * df_ci['nur_liv_pct'] * rec_yrs/10000).astype(int)

    # 护理花费 = 护理花费基数 * 护理消费系数 * 护工个数 * 康复时间
    df_ci['care_spd'] = round(df_ci['care_base'] * care_consp_index_ci * 1 * rec_yrs/10000).astype(int)
    # 低收入的护工个数为0
    df_ci.loc[df_ci['inc_lev_1'] == 1, 'care_spd'] = 0

    # 重疾康复费用 = 营养费 + 护理花费
    df_ci['ci_rec_fee'] = round(df_ci['nutri_fee'] + df_ci['care_spd']).astype(int)

    # 涨幅因子 = 1+涨幅系数
    # 收入补偿 = 收入补偿调整系数 * 个人年收入 * 涨幅因子/（1-涨幅因子） * （1-涨幅因子^（康复年数-1））

    df_ci['inc_comp_fee'] = round(df_ci['inc_comp_ci'] * df_ci['inc'] *\
        (1+df_ci['inc_index_ci']) / (1-(1+df_ci['inc_index_ci'])) *\
            (1-(1+df_ci['inc_index_ci'])**(rec_yrs-1))/10000).astype(int)

    # 无收入家庭成员的个人年收入用当地人均年收入替代，收入补偿调整系数和涨幅系数按照低收入的调整系数计算
    df_ci.loc[df_ci['inc'] == 0, 'inc_comp_ci'] = criti_ill_index_inc['inc_comp_ci'][criti_ill_index_inc['per_inc_lev1'].index(1)]
    df_ci.loc[df_ci['inc'] == 0, 'inc_index_ci'] = criti_ill_index_inc['inc_index_ci'][criti_ill_index_inc['per_inc_lev1'].index(1)]
    #df_ci.loc[df_ci['inc']==0,'inc_comp_ci']=criti_ill_inc_tab.loc[criti_ill_inc_tab['per_inc_lev1']==1,'inc_comp_ci'].values[0]
    #df_ci.loc[df_ci['inc']==0,'inc_index_ci']=criti_ill_inc_tab.loc[criti_ill_inc_tab['per_inc_lev1']==1,'inc_index_ci'].values[0]

    df_ci.loc[df_ci['inc_comp_fee'] == 0, 'inc_comp_fee'] = round(df_ci['inc_comp_ci'] * \
            per_ann_inc_base * (1+df_ci['inc_index_ci']) / (1-(1+df_ci['inc_index_ci'])) * \
                (1-(1+df_ci['inc_index_ci'])**(rec_yrs-1))/10000).astype(int)

    # 重疾险理想保额 = （重疾治疗费用 + 重疾康复费用 + 收入补偿 - 医保报销） * 调整系数
    # 调整系数
    df_ci['ci_amt_index'] = df_ci['age'].apply(adjust_index)


    df_ci['ci_exp_amt'] = round(df_ci['ci_amt_index'] * (df_ci['criti_ill_cost'] + df_ci['ci_rec_fee'] + \
                                                               df_ci['inc_comp_fee'] - df_ci['med_claim'])).astype(int)


    current_app.logger.info("Ci cost %s:", time.time() - time_start)

    #%% 医疗险
    time_start=time.time()

    # 按照不同家庭情况，对保额对应的收入做调整，调整额度基准为家庭最高收入的60%向下取选项值
    def inc_adj_med(inc, support_label):
        income = df_ci['inc'].max() * 0.6
        if ((df_ci['support_label'].sum() == 1) & (support_label == 0)) | \
                ((df_ci['support_label'].sum() == 0) & (inc < income)):
            if income < 200000:
                income_adj = 100000
            elif income < 300000:
                income_adj = 200000
            elif income < 500000:
                income_adj = 300000
            elif income < 800000:
                income_adj = 500000
            elif income < 1000000:
                income_adj = 800000
            elif income < 2000000:
                income_adj = 1000000
            elif income < 3000000:
                income_adj = 2000000
            elif income < 5000000:
                income_adj = 3000000
            elif income >= 5000000:
                income_adj = 5000000
            income_adj = min(income_adj, df_ci['inc'].max())
        else:
            income_adj = inc
        return income_adj

    df_ci['inc_med'] = df_ci.apply(lambda x: inc_adj_med(x['inc'], x['support_label']), axis=1)

    # 不同城市等级对应不同医疗险保额
    if customer_info['city_lev'] == '一级城市':
        df_ci = df_ci.join(med_amt_1,on='inc_med')
    if customer_info['city_lev'] == '二级城市':
        df_ci = df_ci.join(med_amt_2,on='inc_med')
    if customer_info['city_lev'] == '三级城市':
        df_ci = df_ci.join(med_amt_3,on='inc_med')
    if customer_info['city_lev'] == '四级城市':
        df_ci = df_ci.join(med_amt_4,on='inc_med')
    if customer_info['city_lev'] == '五级城市':
        df_ci = df_ci.join(med_amt_5,on='inc_med')

    # 无医保家庭成员保额加15万
    df_ci.loc[df_ci['med'] == 2, 'med_exp_amt_1_adj'] = df_ci.loc[df_ci['med'] == 2, 'med_exp_amt_1_adj'] + 15
    df_ci.loc[df_ci['med'] == 2, 'med_exp_amt_2_adj'] = df_ci.loc[df_ci['med'] == 2, 'med_exp_amt_2_adj'] + 15
    df_ci.loc[df_ci['med'] == 2, 'med_exp_amt_3_adj'] = df_ci.loc[df_ci['med'] == 2, 'med_exp_amt_3_adj'] + 15


    #med_city_tab =pd.DataFrame(med_index_city).set_index('city_lev')
    #df_ci = df_ci.join(med_city_tab,on='city_lev')
    # med_cost_index_med = med_index_city['med_cost_index_med'][med_index_city['city_lev'].index(customer_info['city_lev'])]
    # # 收入水平与其他花费对照表
    # med_inc_tab=pd.DataFrame(med_index_inc)
    # med_ftab_inc = pd.DataFrame({'per_inc_lev1':[6,5,4,3,2,1],
    #                              'ihosp_base_med':[6000,3000,2010,1000,696,100]})
    # med_ftab_inc = med_ftab_inc.merge(med_inc_tab).set_index('per_inc_lev1')
    # df_ci['inc_lev_med'] = df_ci['inc_lev_1'].copy()
    # # 无收入家庭成员收入水平用家庭成员中最高收入水平替代
    # df_ci.loc[df_ci['inc'] == 0, 'inc_lev_med'] = df_ci['inc_lev_med'].max()
    # df_ci = df_ci.join(med_ftab_inc,on='inc_lev_med')
    #
    #
    # # 门诊费用 = 门诊费用基数 * 医疗花费系数
    # df_ci['op_cost_med'] = df_ci['op_base'] * med_cost_index_med
    #
    # # 药品耗材与治疗花费 = （高危重症药品费 + 高危重症治疗费） * 医疗花费系数 * 治疗花费系数 * （1+重疾复发概率）
    # # 高危重症药品费=614328， 高危重症治疗费=220000， 重疾复发概率=30%
    # df_ci['medi_treat_cost_med'] = (614328+220000) * med_cost_index_med * df_ci['tre_spd_index_med'] * (1+0.3)
    #
    # # 住院费 = 每天住院花费基数 * 医疗花费系数 * 高危住院天数
    # # 高危住院天数 = 20
    # df_ci['ihosp_cost_med'] = df_ci['ihosp_base_med'] * med_cost_index_med * 20
    #
    # # 高端医疗花费
    # df_ci['lux_med_cost'] = df_ci['inc'] * df_ci['lux_med_index']
    # # 无收入家庭成员用家庭年收入*0.6替代个人收入
    # # df_ci.loc[df_ci['inc']==0,'lux_med_cost'] = fam_ann_inc*0.6*df_ci['lux_med_index']
    #
    # # 医疗报销 = 15万
    # df_ci['med_claim_med'] = 150000
    # df_ci.loc[df_ci['med'] == 2,'med_claim_med'] = 0
    #
    # ### 医疗险理想保额 = （门诊花费 + 药品耗材与治疗费 + 住院费 - 医保报销） * 调整系数 + 高端医疗花费
    # df_ci['med_exp_amt'] = (df_ci['op_cost_med'] + df_ci['medi_treat_cost_med'] + \
    #                                    df_ci['ihosp_cost_med'] - df_ci['med_claim_med']) * \
    #     df_ci['ci_amt_index'] + df_ci['lux_med_cost']
    #
    #
    # # 医疗调整tab-step 1
    # df_ci[['med_exp_amt_index',
    #     'med_exp_amt_1',
    #     'med_exp_amt_2',
    #     'med_exp_amt_3'
    #     ]] = df_ci.apply(lambda x: medical_adj(x['med_exp_amt']),axis=1,result_type='expand')
    #
    #
    # # 无收入家庭成员用家庭收入最高的成员的医疗保额替代
    # df_ci.loc[df_ci['inc']==0,['med_exp_amt_index','med_exp_amt_1',
    #                        'med_exp_amt_2','med_exp_amt_3']] = \
    #     df_ci.loc[df_ci['inc'].idxmax(),['med_exp_amt_index','med_exp_amt_1',
    #                                      'med_exp_amt_2','med_exp_amt_3']].tolist()
    #
    # # 医疗调整tab-step2
    # def med_adj_s2(index, amt, amt1, amt2, amt3):
    #     # 在原始调整值基础上，原始输出值不变，向低调tab取全家该tab最大值，向上调tab取全家该tab最小值
    #     med_min = df_ci[['med_exp_amt_1','med_exp_amt_2','med_exp_amt_3']].min()
    #     med_max = df_ci[['med_exp_amt_1','med_exp_amt_2','med_exp_amt_3']].max()
    #     if index == 1:
    #         amt1 = amt; amt2 = med_min[1]; amt3 = med_min[2]
    #     elif index == 2:
    #         amt1 = med_max[0]; amt2 = amt; amt3 = med_min[2]
    #     elif index == 3:
    #         amt1 = med_max[0]; amt2 = med_min[1]; amt3 = med_min[2]
    #     return amt1, amt2, amt3
    #
    # df_ci[['med_exp_amt_1','med_exp_amt_2','med_exp_amt_3']] = \
    #     df_ci.apply(lambda x: med_adj_s2(x['med_exp_amt_index'],x['med_exp_amt'],
    #                                      x['med_exp_amt_1'],x['med_exp_amt_2'],
    #                                      x['med_exp_amt_3']), axis=1, result_type='expand')
    #
    # # 全部家庭成员的默认显示tab为最高收入者的tab
    # df_ci['med_exp_amt_index'] = df_ci['med_exp_amt_index'].max()
    #
    # # 一级参数输出调整
    # df_ci['med_exp_amt_1_adj'] = df_ci['med_exp_amt_1'].apply(adjust_fc_par)
    # df_ci['med_exp_amt_2_adj'] = df_ci['med_exp_amt_2'].apply(adjust_fc_par)
    # df_ci['med_exp_amt_3_adj'] = df_ci['med_exp_amt_3'].apply(adjust_fc_par)

    current_app.logger.info("Med cost %s:", time.time() - time_start)
    #%% 养老保险
    time_start = time.time()
    '''
     娱乐偏好：
     1   低  Q26 为1，2，3
     2   中  Q26 为 4，5
     3   高  Q26 为 6，7
     ----
     b15 娱乐消费系数
     '''

    def ent_pref(x):
        if x in [1, 2, 3]:
            ent_pref = 1;
            b15 = 0.5
        elif x in [4, 5]:
            ent_pref = 2;
            b15 = 1
        elif x in [6, 7]:
            ent_pref = 3;
            b15 = 1.5
        return ent_pref, b15

    # 娱乐偏好
    ent_pref,b15 = ent_pref(customer_info['Q26'])

    #养生健康消费基数
    per_avg_ann_exp = customer_info['per_avg_ann_exp']
    health_exp_index = per_avg_ann_exp*0.2

    #疾病管理消费基数
    dis_exp_index = per_avg_ann_exp*0.2

    #社交娱乐消费基数
    entm_exp_index = per_avg_ann_exp*0.1

    # 年龄范围
    df_ci['age_stage_2'] = df_ci['age'].apply(age_stage2)


    # 对于收入不到家庭总收入10%的家庭成员，对其收入调整为家庭总收入的10%并向下调至收入选项，并进行后续计算
    def inc_adj_endw(inc):
        income = fam_ann_inc * 0.1
        if inc < income:
            if income < 200000:
                income_adj = 100000
            elif income < 300000:
                income_adj = 200000
            elif income < 500000:
                income_adj = 300000
            elif income < 800000:
                income_adj = 500000
            elif income < 1000000:
                income_adj = 800000
            elif income < 2000000:
                income_adj = 1000000
            elif income < 3000000:
                income_adj = 2000000
            elif income < 5000000:
                income_adj = 3000000
            elif income >= 5000000:
                income_adj = 5000000
        else:
            income_adj = inc
        return income_adj

    # 无收入家庭成员取家庭总收入*0.6后取近
    df_ci['inc_2'] = df_ci['inc'].apply(inc_adj_endw)
    print(df_ci['inc_2'])
    # 个人收入范围
    df_ci['inc_lev_2']=df_ci['inc_2'].apply(income_level2)

    # 养老系数映射表1
    elder_inc_tab = pd.DataFrame(elder_index_inc).set_index('per_inc_lev2')

    # 养老系数映射表2
    sheet2 = pd.DataFrame(elder_index_city).set_index('city_lev')

    # 年龄涨幅映射表
    agestage = pd.DataFrame(elder_index_age).set_index('age_stage2')


    df_ci = df_ci.join(elder_inc_tab,on='inc_lev_2')
    df_ci = df_ci.join(agestage, on='age_stage_2')
    df_ci = df_ci.join(sheet2,on='city_lev')

    # 保障覆盖年数
    expt_secur_ann = cover_age - 60


    # 预期通胀率 exp_inf_rate和2取小,当预期通胀率小于1时，通胀率为1
    df_ci['exp_inf_rate_base'] = (1+infla_rate)**(60-df_ci['age'])
    # df_ci['inf_rate_contrast'] = 2
    df_ci['exp_inf_rate'] = df_ci['exp_inf_rate_base'].apply(lambda x: x if 1<x<=2 else(2 if x>2 else 1))


    # 基础日常生活支出=人均日常生活消费基数per_avg_ann_exp*日常生活消费系数b1*保障覆盖年数*预期通胀率
    df_ci['bac_daily_consp_exp'] = per_avg_ann_exp*df_ci['b1']*expt_secur_ann*df_ci['exp_inf_rate']

    # 额外养生健康支出=人均养生健康消费基数 health_exp_index*养生健康消费系数b4*养生健康消费系数b6*保障覆盖年数*预期通胀率
    df_ci['extra_health_spd'] = health_exp_index*df_ci['b4']*b6*expt_secur_ann*df_ci['exp_inf_rate']

    # 额外疾病管理支出=疾病管理消费基数 dis_exp_index*疾病管理消费系数b8*疾病管理消费系数b10*保障覆盖年数*预期通胀率
    df_ci['extra_medi_mgmt_exp'] = dis_exp_index*df_ci['b8']*b10*expt_secur_ann*df_ci['exp_inf_rate']

    # 额外社交娱乐支出=社交娱乐消费基数 entm_exp_index*娱乐消费系数b12*娱乐消费系数b14*娱乐消费系数b15*保障覆盖年数*预期通胀率
    df_ci['extra_entm_exp'] = entm_exp_index*df_ci['b12']*b14*b15*expt_secur_ann*df_ci['exp_inf_rate']

    # 老年个人消费支出=基础日常生活支出+额外养生健康支出+额外疾病管理支出+额外社交娱乐支出
    df_ci['elder_pers_consp_exp'] = df_ci[['bac_daily_consp_exp','extra_health_spd',
                                           'extra_medi_mgmt_exp','extra_entm_exp']].apply(lambda x:x.sum(),axis =1)


    # 收入替代金额=当前年收入*涨幅倍数*年龄涨幅系数*收入替代率*覆盖年数
    df_ci['inc_rep_amount'] = df_ci['inc_2']*df_ci['inc_raise']*df_ci['inc_coef']*df_ci['inc_rep']
    df_ci['inc_rep_amount_all'] = df_ci['inc_rep_amount']*expt_secur_ann

    # 个人养老金
    p = df_ci[['inc_2']]*0.24*30
    # 年收入对照值
    p['avg_inc_contrast']=96317*0.24*3*30

    df_ci['pers_annt'] = p[['inc_2','avg_inc_contrast']].min(axis=1)

    # 养老理想保额计算结果
    #公式一：老年个人消费支出-个人养老金*预期通胀率
    df_ci['endw_expt_amt1'] = df_ci['elder_pers_consp_exp']-df_ci['pers_annt']*df_ci['exp_inf_rate']

    #公式二：(收入替代金额-个人养老金)*收入替代系数
    df_ci['endw_expt_amt2'] = (df_ci['inc_rep_amount_all']-df_ci['pers_annt'])*df_ci['inc_rep_coef']

    #取两公式结果的最大值
    df_ci['endw_expt_amt'] = df_ci[['endw_expt_amt1','endw_expt_amt2']].max(axis=1)

    #若取得公式一（即个人消费支出法）则为1，公式二（即收入替代法）则为0
    df_ci['endw_formula'] = df_ci.apply(lambda x: compare(x['endw_expt_amt1'],x['endw_expt_amt2']),axis=1)

    # 在个人消费支出法中，所有输出均为乘以通胀因子后的结果
    df_ci.loc[df_ci['endw_formula'] == 1, 'pers_annt'] = df_ci['pers_annt'] * df_ci['exp_inf_rate']


    # 个人收入10万~30万，默认输出基础养老（1）；50万~100万，默认输出无忧养老（2），200万~500万，默认输出舒适养老（3）
    df_ci['endw_expt_amt_index'] = df_ci['inc_2'].apply(lambda x: \
                                                 1 if x <=300000 else (2 if x<=1000000 else 3))
    print(df_ci['endw_expt_amt_index'])


    # 默认预计每月可领取 = 默认养老险金额 / 20 / 12(精确到百元，单位元)
    df_ci['endw_mon_amt'] = (round(df_ci['endw_expt_amt']/240/100)*100).astype(int)
    df_ci['endw_expt_amt_adj'] = round(df_ci['endw_mon_amt']*240/10000).astype(int)
    
    # 调整结果

    df_ci[['endw_mon_amt_1','endw_mon_amt_2','endw_mon_amt_3']] = \
        df_ci.apply(lambda x: endow_adj(x['endw_expt_amt_index'],x['endw_mon_amt']),
                    axis=1, result_type='expand')

    # 养老险一级参数调整按照每月可领取金额*240/10000计算 （单位万元）
    t0 = time.time()
    df_ci['endw_expt_amt_1_adj'] = round(df_ci['endw_mon_amt_1']*240/10000).astype(int)
    df_ci['endw_expt_amt_2_adj'] = round(df_ci['endw_mon_amt_2']*240/10000).astype(int)
    df_ci['endw_expt_amt_3_adj'] = round(df_ci['endw_mon_amt_3']*240/10000).astype(int)
    
    # df_ci['elder_pers_consp_exp_1'] = df_ci['endw_expt_amt_1_adj'] + df_ci['pers_annt']
    # df_ci['elder_pers_consp_exp_2'] = df_ci['endw_expt_amt_2_adj'] + df_ci['pers_annt']
    # df_ci['elder_pers_consp_exp_3'] = df_ci['endw_expt_amt_3_adj'] + df_ci['pers_annt']

    endow_list1 = ['bac_daily_consp_exp','extra_health_spd','extra_medi_mgmt_exp',
                   'extra_entm_exp','pers_annt','inc_rep_amount']

    # 养老险二、三级参数调整（单位万元）
    for var in endow_list1:
        df_ci['{0}_adj'.format(var)] = df_ci.apply(lambda x: adjust_stc_par(x['endw_expt_amt'],
                                                                      x['endw_expt_amt_adj'],
                                                                      x[var]),axis=1)

    df_ci['elder_pers_consp_exp_1_adj'] = df_ci['endw_expt_amt_1_adj'] + df_ci['pers_annt_adj']

    df_ci['elder_pers_consp_exp_2_adj'] = df_ci['endw_expt_amt_2_adj'] + df_ci['pers_annt_adj']

    df_ci['elder_pers_consp_exp_3_adj'] = df_ci['endw_expt_amt_3_adj'] + df_ci['pers_annt_adj']
    
    current_app.logger.info('Recalculate pers var %s', time.time()-t0)
    current_app.logger.info("Pers cost %s:", time.time() - time_start)

    current_app.logger.info("Formula complete %s:", time.time()-object_start_time)

    #%% 结果整理输出
    p_time_start = time.time()
    result = df_ci[['life_amt','acc_exp_amt','acc_due','acc_med_spd','support_label', # 寿险、意外险
                'criti_ill_cost','op_cost','medi_treat_cost','ihosp_cost','med_claim',
                'ci_rec_fee','nutri_fee','care_spd','inc_comp_fee','ci_exp_amt', # 重疾险
                'med_exp_amt_1_adj','med_exp_amt_2_adj','med_exp_amt_3_adj','med_exp_amt_index', # 医疗险
                'elder_pers_consp_exp_1_adj','elder_pers_consp_exp_2_adj','elder_pers_consp_exp_3_adj',
                'bac_daily_consp_exp_adj','extra_health_spd_adj','extra_medi_mgmt_exp_adj','extra_entm_exp_adj',
                'inc_rep_amount_adj','pers_annt_adj','endw_formula','endw_expt_amt_1_adj','endw_mon_amt_1',
                'endw_expt_amt_2_adj','endw_mon_amt_2','endw_expt_amt_3_adj','endw_mon_amt_3',
                'endw_expt_amt_index']]
    # 寿险家庭部分
    result = result.join(df[['fam_fur_spd','fam_liv_spd','chi_edu_spd','tcare_par_spd','chi_marr_spd','ass_inh_amt','curr_debt','fixed_savi']])
    # 孩子教育险
    if chi_num != 0:
        result = result.join(df_educate[['stafe_misc_fee_1_adj','accum_diff_stage_extra_edu_spd_1_adj','accum_liv_exp_1_adj','edu_expt_amt_infla_1_adj',
                     'stafe_misc_fee_2_adj','accum_diff_stage_extra_edu_spd_2_adj','accum_liv_exp_2_adj','edu_expt_amt_infla_2_adj',
                     'edu_expt_amt_index']])
    result[result<0] = 0

    current_app.logger.info("Data rearrange %s:", time.time() - p_time_start)
    current_app.logger.info("Whole time %s:", time.time() - object_start_time)

    return jsonify({'model': label, 'formula': result.T.to_dict()})



